# 在线学生信息管理
# 
# 导入数据库模块
import pymysql
# stu表信息操作类
class Student:
	# 构造方法实现数据库连接
	def __init__(self):
		self.db = pymysql.connect(host="localhost",user="root",password="",db="mydb",charset="utf8")

	# findAll() 查询方法
	def findAll(self):
		cursor = self.db.cursor() # 创建游标对象
		sql = "select * from stu" # 定义sql语句
		try:
			cursor.execute(sql) # 执行sql语句
			print("查询结果条数：",cursor.rowcount)
			stuList = cursor.fetchall() # 解析遍历
			for s in stuList:
				print(s)
		except Exception as err:
			print("SQL执行有误，原因是：",err)

	# del(id) 删除方法
	def delID(self,id):
		cursor = self.db.cursor()
		try:
			sql = "delete from stu where id='%d'"%(id)
			n = cursor.execute(sql)
			self.db.commit()	# 事务提交
			print("成功删除记录条数：",n)
		except Exception as err:
			self.db.rollback() #事务回滚
			print("SQL执行有误，原因是：",err)

	# insert(data) 添加方法
	def insert(self,data):
		cursor = self.db.cursor()
		try:
			sql = "insert into stu(name,age,sex,classid) values('%s','%d','%s','%s')"%(data)
			n = cursor.execute(sql)
			self.db.commit()	# 事务提交
			print("成功添加记录条数：",n)
		except Exception as err:
			self.db.rollback() #事务回滚
			print("SQL执行有误，原因是：",err)

	# 析构方法关闭数据库连接
	def __del__(self):
		self.db.close()

while True:
	# 初始化在线学生信息管理系统界面
	print("="*20,"在线学生信息管理系统","="*20)
	print("{0:1}{1:13}{2:15}".format(" ","1.查看所有学生信息","2.添加学生信息"))
	print("{0:1}{1:13}{2:15}".format(" ","3.删除学生信息","4.退出系统"))
	print("="*40)

	# 创建学生对象
	stu0 = Student()

	# 根据键盘输入值，判断下一步操作
	key = input("请输入你的选择:")
	if key == '1':
		stu0.findAll()
	elif key == '2':
		try:
			name = input("请输入姓名：")
			age = int(input("请输入年龄："))
			sex = input("请输入性别(m|w)：")
			classid = input("请输入班级号：")
		except TypeError as err:
			print("输入信息有误，原因是：",err)
		data = (name,age,sex,classid)
		stu0.insert(data)
	elif key == '3':
		try:
			dId = int(input("请输入需要删除的id号："))
		except TypeError as err:
			print("输入信息有误，原因是：",err)
		stu0.delID(dId)
	elif key == '4':
		print("退出系统...")
	else:
		print("无效键盘输入！")

	key = input("按回车键继续...")